﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;

namespace CommunitySystem
{
    public class GuaZhiDuanLianBO
    {
        #region 安置计划
        public ObservableCollection<GuaZhiDuanLianJiHuaModel> GetGuaZhiDuanLianJiHuaModel(int zhaoluid, string islocked,int year)
        {
            ObservableCollection<GuaZhiDuanLianJiHuaModel> rights = new ObservableCollection<GuaZhiDuanLianJiHuaModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY id ) Sec,* from GuaZhiDuanLianJiHua  where 1=1 ");
            if (zhaoluid != 0)
            {
                sql += " and ID = " + zhaoluid;
            }
            if (year != 0)
            {
                sql += " and Year = " + year;
            }
            if (!string.IsNullOrEmpty(islocked))
            {
                sql += " and Status = '" + islocked + "'";
            }
            else
            {
                sql += " and Status != '" + islocked + "'";
            }
            sql += " order by id desc";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(GuaZhiDuanLianJiHuaModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 用于招录职位的条件上的招录计划名称combobox
        /// </summary>
        /// <param name="zhaoluid"></param>
        /// <param name="islocked"></param>
        /// <returns></returns>
        public ObservableCollection<GuaZhiDuanLianJiHuaModel> GetGuaZhiDuanLianJiHuaModelForCom(string status)
        {
            ObservableCollection<GuaZhiDuanLianJiHuaModel> rights = new ObservableCollection<GuaZhiDuanLianJiHuaModel>();
            string sql = string.Format(@"select * from GuaZhiDuanLianJiHua  where 1=1 ");
            if (!string.IsNullOrEmpty(status))
            {
                sql += " and Status = '" + status + "'";
            }
            else
            {
                sql += " and Status != '" + status + "'";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(GuaZhiDuanLianJiHuaModel.FillEntity(dr));
            }
            return rights;
        }

        public int InsertJuZhuanAnZhiJiHuaTitle(GuaZhiDuanLianJiHuaModel ZhaoLuJiHua)
        {
            string sql = "";
            if (ZhaoLuJiHua != null)
            {
                if (ZhaoLuJiHua.ID == 0)
                    sql = string.Format(@"INSERT INTO GuaZhiDuanLianJiHua
           ([Year]
           ,[Name]
           ,[IsLocked],Status)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}','{3}')  ;select @@IDENTITY;", ZhaoLuJiHua.Year, ZhaoLuJiHua.Name, ZhaoLuJiHua.IsLocked, "前期准备");
                else
                    sql = string.Format("update GuaZhiDuanLianJiHua set Name = '{0}' where id = {1};select 0 from GuaZhiDuanLianJiHua", ZhaoLuJiHua.Name,
                                        ZhaoLuJiHua.ID);
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            else return 0;
        }

        public bool DeleteJuZhuanAnZhiJiHua(int id)
        {
            string sql = string.Format(@"delete from GuaZhiDuanLianJiHua where id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool SuoJuZhuanAnZhiJiHua(int id)
        {
            //            string sql = @"update dbo.ZhaoLuZhiWei set islocked = 1 where islocked = 0;
            //update ZhaoLuJiHua set IsLocked = 1 where ID in
            //(select ZhaoLuJiHuaID from ZhaoLuJiHuaDetail1 where ZhaoLuZhiWeiID in
            //(select ID from ZhaoLuZhiWei where IsLocked = 0)) ";
            string sql = @"update GuaZhiDuanLianJiHua set Status = '已结束' where id = " + id;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        public ObservableCollection<Emp_BasicInfoModel> GetGuaZhiDuanLianEmpModel(string empids)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY emp.ID ) Sec,emp.ID, o.id OrgID,o.Name OrgName,emp.Name,emp.Gender,emp.Birthday,emp.NowWorkDate,emp.Political,edc.EducationName,
a.DegreeName EducationType,ed.DutyName,ed.DutyNo,ed.DutyLevel,ed.DutyAttribute,ed.JobDate,ed.RXRZWSXSJ from Emp_BasicInfo emp
inner join OrgForHR o on o.ID = emp.OrgID
 left join Emp_Duty ed on emp.ID = ed.EmpbasicID and ed.JobStatus = '在任'
 left join Emp_Education edc on emp.ID = edc.EmpBasicID and IsTopEducation = 1
 left join 
 (select top 1 EmpbasicID,DegreeName from Emp_Degree order by EducationDate desc) a on a.EmpbasicID = emp.ID
 where 1=1 ");
            if (!string.IsNullOrEmpty(empids))
            {
                sql += " and emp.ID in (" + empids + ")";
            }
            sql += " order by o.ID,emp.ID";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(Emp_BasicInfoModel.FillEntity(dr));
            }
            return rights;
        }

        public bool InsertGuaZhiEmp(string empids,int exameid)
        {
            ObservableCollection<Emp_BasicInfoModel> rights = new ObservableCollection<Emp_BasicInfoModel>();
            rights = GetGuaZhiDuanLianEmpModel(empids);
            string sql = "";
            if(rights!= null && rights.Count > 0)
            {
                foreach(Emp_BasicInfoModel i in rights)
                {
                    sql += string.Format(@"INSERT INTO GuaZhiEmp
           ([EmpBasicID]
           ,[OrgName]
           ,[Name]
           ,[Gender]
           ,[Birthday]
           ,[NowWorkDate]
           ,[Political]
           ,[EducationName]
           ,[DegreeName]
           ,[DutyName]
           ,[DutyNo]
           ,[DutyLevel]
           ,[DutyAttribute]
           ,[Status]
           ,[JiHuaID],OrgID,JobDate,RXRZWSXSJ,YanQiStatus)
     VALUES
           ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',{14},{15},'{16}','{17}','{18}');",
                 i.ID, i.OrgName, i.Name, i.Gender, i.Birthday, i.NowWorkDate, i.Political, i.EducationName, i.EducationType,i.DutyName,i.DutyNo,
                 i.DutyLevel, i.DutyAttribute, "未审核", exameid, i.OrgID, i.JobDate, i.RXRZWSXSJ, "未审核");
                }
                
            }
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public ObservableCollection<GuaZhiEmpModel> GetGuaZhiDuanLianEmpZongModel(int jihuaid, string orgids, string status, ObservableCollection<int> id)
        {
            ObservableCollection<GuaZhiEmpModel> rights = new ObservableCollection<GuaZhiEmpModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY gze.ID ) Sec,gze.* from GuaZhiEmp gze inner join GuaZhiDuanLianJiHua g on gze.JiHuaID = g.ID where 1=1 ");
            if (jihuaid != 0)
            {
                sql += " and g.ID =" + jihuaid ;
            }
            if (id != null && id.Count > 0)
            {
                string ss = "";
                foreach (int i in id)
                    ss += i + ",";
                sql += " and gze.ID in (" + ss.Substring(0,ss.Length - 1) + ")";
            }
            if(!string.IsNullOrEmpty(orgids) && orgids != "0")
            {
                sql += " and OrgID in (" + orgids + ")";
            }
            if (!string.IsNullOrEmpty(status))
            {
                if (!status.Contains(','))
                    sql += " and  gze.Status ='" + status + "'";
                else
                    sql += " and gze.Status in (" + status + ")";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                GuaZhiEmpModel khm = new GuaZhiEmpModel();
                khm = GuaZhiEmpModel.FillEntity(dr);
                ObservableCollection<GuaZhi_AttatchmentModel> temp = GetZhaoLuZhengShen_Attatchment(Convert.ToInt32(dr["ID"]));
                if (temp != null && temp.Count > 0)
                {
                    foreach (GuaZhi_AttatchmentModel i in temp)
                    {
                        if (!khm.Attachment.Contains(i))
                            khm.Attachment.Add(i);
                    }
                }
                rights.Add(khm);
            }
            return rights;
        }

        /// <summary>
        /// 通过审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheckGuaZhiEmp(string ID)
        {
            string sql = "update dbo.GuaZhiEmp set Status = '审核通过' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 退回
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool TuiGuaZhiEmp(string ID, string memo)
        {
            string sql = "update dbo.GuaZhiEmp set Status = '已退回',Demo = '" + memo + "' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 取消审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CancelGuaZhiEmp(string ID)
        {
            string sql = "update dbo.GuaZhiEmp set Status = '未审核',Demo = '' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool ZhiWeiAnPai(string ID, GuaZhiEmpModel model, ObservableCollection<GuaZhiEmpModel> list)
        {
            string s = "";
            string orgn = "";
            string sql1 = "";
            if (model.GuaZhiOrgID != 0)
            {
                s = "select Name from OrgForHR where ID =  " + model.GuaZhiOrgID;
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(s);
                if(ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count >0)
                {
                    orgn = ds.Tables[0].Rows[0][0].ToString();
                }
            }
            string sql = string.Format(@"update GuaZhiEmp set GuaZhiOrgID = {0},GuaZhiOrgName = '{1}',GuaZhiShangJiOrgID = {2},GuaZhiZhiWu = '{3}',
GuaZhiBeginDate = '{4}',ZhongZhiDate = '{5}',Demo = '{6}',status = '挂职中' where ID in ({7});
                update GuaZhiDuanLianJiHua set Status = '进行中' where ID = {8};", model.GuaZhiOrgID, model.GuaZhiOrgID == 0 ? model.GuaZhiOrgName : orgn, model.GuaZhiShangJiOrgID,
            model.GuaZhiZhiWu,model.GuaZhiBeginDate,model.ZhongZhiDate,model.Demo,ID,model.JiHuaID);
            foreach (GuaZhiEmpModel i in list)
            {
                sql1 += string.Format(@"insert into Emp_Duty (EmpbasicID,DutyLevel,DutyAttribute,OKDate,OrganizationName,WorkCharge,JobDate,RXRZWSXSJ,JobStatus,GRStartDate,WAiID)
values ({9},'{10}','{11}','{4}','{1}','{3}','{12}','{13}','挂任','{4}',{16});
insert into Emp_Resume (EmpbasicID,BeginDate,Unit,Duty,IsPeriod,WAiID)
values ({9},'{4}','{1}','{14}','{15}',{16})", model.GuaZhiOrgID, model.GuaZhiOrgID == 0 ? model.GuaZhiOrgName : orgn, model.GuaZhiShangJiOrgID,
            model.GuaZhiZhiWu, model.GuaZhiBeginDate, model.ZhongZhiDate, model.Demo, ID, model.JiHuaID,
            i.EmpbasicID, model.DutyLevel, model.DutyAttribute, model.JobDate, model.RXRZWSXSJ, model.GuaZhiZhiWu + "（挂职锻炼）", 1,i.ID);
            }
            if (!string.IsNullOrEmpty(sql + sql1))
                AccessHelper.GetInstance().ExecuteNonQuery(sql + sql1);
            return true;
        }

        public bool UpdateShiJian(GuaZhiEmpModel model)
        {
            string sql = "update dbo.GuaZhiEmp set ShiJianAnPai = '" + model.ShiJianAnPai +"' where ID = " + model.ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool YanQiGuaZhiEmp(string ID, string memo,DateTime? riqi)
        {
            string sql = "update dbo.GuaZhiEmp set status = '挂职中-延期申请', YanQiDate = '" + riqi + "',YanQiReason = '" + memo + "' where ID in (" + ID + ")";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool ShenHeYanQiGuaZhiEmp(string ID,string istongyi)
        {
            string sql = "";
            if(istongyi == "同意")
               sql = @"update dbo.GuaZhiEmp set status = '挂职中-延期',Demo = Demo + ',延期至' + 
CONVERT(varchar, YEAR(YanQiDate)) + '年' + CONVERT(varchar, MONTH(YanQiDate)) + '月' + CONVERT(varchar, day(YanQiDate)) + '日' where ID in  (" + ID + ")";
            if(istongyi == "不同意")
                sql = @"update dbo.GuaZhiEmp set status = '挂职中' where ID in  (" + ID + ")";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool ZhongZhiGuaZhiEmp(string ID, string memo, DateTime? riqi)
        {
            string sql = "update dbo.GuaZhiEmp set status = '终止', ZhongZhiDate = '" + riqi + "',ZhongZhiReason = '" + memo + "',Demo = Demo + ',' + '" + memo + "' where ID in (" + ID + ")";
            sql += "delete from Emp_Duty where WAiID in (" + ID + ")";
            sql += "delete from Emp_Resume where WAiID in (" + ID + ")";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool ShenHeShenHeYanQiGuaZhiEmp(string ID)
        {
            string sql = @"update dbo.GuaZhiEmp set status = '终止',Demo = Demo + ',' + ZhongZhiReason where ID in  (" + ID + ")";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool GetGuaZhiDuanLianEmps(int jihuaid, string empbasicids)
        {
            string sql = string.Format(@"select COUNT(ID) cou from GuaZhiEmp where Status != '终止' ");
            if (jihuaid != 0)
            {
                sql += " and JiHuaID =" + jihuaid;
            }
            if (!string.IsNullOrEmpty(empbasicids) )
            {
                sql += " and EmpBasicID in (" + empbasicids + ")";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()) && Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0)
                    return false;
                else return true;
            }
            else return true;
        }

        public bool UpdateGuaZhi_ZhengShen(GuaZhiEmpModel model)
        {
            string sql2 = string.Format(@"delete from GuaZhi_Attatchment where mainid = {0} ", model.ID,model.Attachment[0].Type);
                if (!string.IsNullOrEmpty(sql2))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql2);
                foreach (GuaZhi_AttatchmentModel i in model.Attachment)
                {
                    string sql1 = string.Format(@"INSERT INTO GuaZhi_Attatchment
           ([MainID]
           ,[Key]
           ,[filename]
           ,[realname],Status,FileSize,Percentage,type)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}','{4}','{5}','{6}','{7}')", model.ID, i.Key, i.filename, i.realname, i.Status, i.FileSize, i.Percentage, i.Type);
                    if (!string.IsNullOrEmpty(sql1))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                }
            
            return true;
        }

        public ObservableCollection<GuaZhi_AttatchmentModel> GetZhaoLuZhengShen_Attatchment(int id)
        {
            ObservableCollection<GuaZhi_AttatchmentModel> rights = new ObservableCollection<GuaZhi_AttatchmentModel>();
            string sql = "";
            sql = string.Format(@"select * from GuaZhi_Attatchment where mainid = {0}", id);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(GuaZhi_AttatchmentModel.FillEntity(dr));
            }
            return rights;
        }

        public bool GuaZhiJieShu(string ID,DateTime? dt,int guazhijihuaid)
        {
            string sql = string.Format(@"update Emp_Duty set GREndDate = '{0}' where WAiID in ({1})
    update Emp_Resume set EndDate = '{0}' where WAiID in ({1})
    update GuaZhiEmp set ZhongZhiDate = '{0}' where ID in ({1})
    update GuaZhiEmp set Status = '结束' where ID in ({1}) and ZhongZhiDate < GETDATE()
    update GuaZhiDuanLianJiHua set Status = '已结束' where id = {2} and 
    (select COUNT(ID) cou from GuaZhiEmp where JiHuaID = {2} and Status != '已结束') <= 0", dt, ID, guazhijihuaid);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
    }
}
